using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

using SIS.ENT;


namespace SIS.DAL
{
    /// <summary>
    /// The CourseDetailTableDB class is responsible for interacting with the database to retrieve and store information
    /// about CourseDetailTable objects.
    /// </summary>
    public class CourseDetailTableDB
    {
        #region "Public Methods"

        /// <summary>
        /// Gets an instance of CourseDetailTable from the underlying datasource.
        /// </summary>
        /// <param name="courseDetailKey">The unique CourseDetailKey of the CourseDetailTable in the database.</param>
        /// <returns>An CourseDetailTable when the CourseDetailKey was found in the database, or null otherwise.</returns>
        public static CourseDetailTable GetItem(int courseDetailKey)
        {
            CourseDetailTable myCourseDetailTable = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocCourseDetailTableSelectSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@courseDetailKey", courseDetailKey);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            myCourseDetailTable = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
                return myCourseDetailTable;
            }
        }

        /// <summary>
        /// Returns a list with CourseDetailTable objects.
        /// </summary>
        /// <returns>A generics List with the CourseDetailTable objects.</returns>
        public static CourseDetailTableList GetList()
        {
            CourseDetailTableList tempList = new CourseDetailTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocCourseDetailTableSelectList", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }

        /// <summary>
        /// Returns a list with CourseDetailTable objects.
        /// </summary>
        /// <returns>A generics List with the CourseDetailTable objects.</returns>
        public static CourseDetailTableList GetListByCourseKey(int CourseKey)
        {
            CourseDetailTableList tempList = new CourseDetailTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocCourseDetailTableSelectListByCourseKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@courseKey", CourseKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with CourseDetailTable objects.
        /// </summary>
        /// <returns>A generics List with the CourseDetailTable objects.</returns>
        public static CourseDetailTableList GetListByStandardLevelKey(int StandardLevelKey)
        {
            CourseDetailTableList tempList = new CourseDetailTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocCourseDetailTableSelectListByStandardLevelKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@standardLevelKey", StandardLevelKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with CourseDetailTable objects.
        /// </summary>
        /// <returns>A generics List with the CourseDetailTable objects.</returns>
        public static CourseDetailTableList GetListBySubjectKey(int SubjectKey)
        {
            CourseDetailTableList tempList = new CourseDetailTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocCourseDetailTableSelectListBySubjectKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@subjectKey", SubjectKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Saves a CourseDetailTable in the database.
        /// </summary>
        /// <param name="myCourseDetailTable">The CourseDetailTable instance to save.</param>
        /// <returns>The new CourseDetailKey if the CourseDetailTable is new in the database or the existing CourseDetailKey when an item was updated.</returns>
        public static int Save(CourseDetailTable myCourseDetailTable)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocCourseDetailTableInsertUpdateSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    if (myCourseDetailTable.CourseDetailKey == -1)
                    {
                        myCommand.Parameters.AddWithValue("@courseDetailKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@courseDetailKey", myCourseDetailTable.CourseDetailKey);
                    }
                    if (myCourseDetailTable.CourseKey == null)
                    {
                        myCommand.Parameters.AddWithValue("@courseKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@courseKey", myCourseDetailTable.CourseKey);
                    }
                    if (myCourseDetailTable.StandardLevelKey == null)
                    {
                        myCommand.Parameters.AddWithValue("@standardLevelKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@standardLevelKey", myCourseDetailTable.StandardLevelKey);
                    }
                    if (myCourseDetailTable.SubjectKey == null)
                    {
                        myCommand.Parameters.AddWithValue("@subjectKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@subjectKey", myCourseDetailTable.SubjectKey);
                    }
                    if (myCourseDetailTable.DayGroupCode == null)
                    {
                        myCommand.Parameters.AddWithValue("@dayGroupCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@dayGroupCode", myCourseDetailTable.DayGroupCode);
                    }
                    if (string.IsNullOrEmpty(myCourseDetailTable.Description))
                    {
                        myCommand.Parameters.AddWithValue("@description", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@description", myCourseDetailTable.Description);
                    }
                    if (myCourseDetailTable.Status == null)
                    {
                        myCommand.Parameters.AddWithValue("@status", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@status", myCourseDetailTable.Status);
                    }
                    if (myCourseDetailTable.CreateDateTime == null)
                    {
                        myCommand.Parameters.AddWithValue("@createDateTime", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@createDateTime", myCourseDetailTable.CreateDateTime);
                    }
                    if (myCourseDetailTable.ModifiedDateTime == null)
                    {
                        myCommand.Parameters.AddWithValue("@modifiedDateTime", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@modifiedDateTime", myCourseDetailTable.ModifiedDateTime);
                    }

                    DbParameter returnValue;
                    returnValue = myCommand.CreateParameter();
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    myCommand.Parameters.Add(returnValue);

                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    result = Convert.ToInt32(returnValue.Value);
                    myConnection.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Deletes a CourseDetailTable from the database.
        /// </summary>
        /// <param name="courseDetailKey">The CourseDetailKey of the CourseDetailTable to delete.</param>
        /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
        public static bool Delete(int courseDetailKey)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocCourseDetailTableDeleteSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@courseDetailKey", courseDetailKey);
                    myConnection.Open();
                    result = myCommand.ExecuteNonQuery();
                    myConnection.Close();
                }
            }
            return result > 0;
        }

        #endregion

        /// <summary>
        /// Initializes a new instance of the CourseDetailTable class and fills it with the data fom the IDataRecord.
        /// </summary>
        private static CourseDetailTable FillDataRecord(IDataRecord myDataRecord)
{
CourseDetailTable myCourseDetailTable = new CourseDetailTable();
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CourseDetailKey")))
{
myCourseDetailTable.CourseDetailKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("CourseDetailKey"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CourseKey")))
{
myCourseDetailTable.CourseKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("CourseKey"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("StandardLevelKey")))
{
myCourseDetailTable.StandardLevelKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("StandardLevelKey"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("SubjectKey")))
{
myCourseDetailTable.SubjectKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("SubjectKey"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DayGroupCode")))
{
myCourseDetailTable.DayGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DayGroupCode"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Description")))
{
myCourseDetailTable.Description = myDataRecord.GetString(myDataRecord.GetOrdinal("Description"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
{
myCourseDetailTable.Status = myDataRecord.GetBoolean(myDataRecord.GetOrdinal("Status"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreateDateTime")))
{
myCourseDetailTable.CreateDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreateDateTime"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
{
myCourseDetailTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
}
return myCourseDetailTable;
}
    }

}
